	package browser.service;

	import browser.dao.UserInfo;
	import java.io.*;
	import java.sql.*;
	import javax.servlet.*;
	import javax.servlet.http.*;

	/**
	 * Servlet that will display operation menu on the left side of the page.
	 * Contents: table list, procedures, functions, packages, views
	 */
	public class OPMenu extends HttpServlet {

		public static Connection conn;
		public static Statement stmt;

		public void doGet(HttpServletRequest request,
				HttpServletResponse response) throws ServletException,
				IOException {

			doPost(request, response);
		}

		public void doPost(HttpServletRequest request,
				HttpServletResponse response) throws ServletException,
				IOException {

			response.setContentType("text/html");
			PrintWriter out = response.getWriter();

			// Check if session is expired
			if (request.getSession(false) == null
					|| (Boolean) request.getSession(false).getAttribute(
							"expired") == true) {
				out.println("Session expired");
				return;
			}

			String uid[] = request.getParameterValues("uid");
			String uacc[] = request.getParameterValues("uAccess");

			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
			} catch (ClassNotFoundException e) {
				out.println("Error loading the Driver:" + e.getMessage());
				return;
			}

			Connection conn = null;
			UserInfo user = MyUtilities.getUserInfo(uid[0]);

			if ((user.getUaccess() == null)
					|| (!uacc[0].equals(user.getUaccess()))) {
				out.print("Please login first");
				return;
			}

			try {
				conn = DriverManager.getConnection("jdbc:oracle:thin:"
						+ user.getUid() + "/" + user.getPassword()
						+ "@oraserver.cs.luc.edu:1521:cs");
			} catch (SQLException e1) {
				out.println("Error connecting to Oracle:" + e1.getMessage());
				return;
			}
			if (conn == null) {
				out.println("Null Connection");
				return;
			}

			Statement stmt = null;
			try {
				stmt = conn.createStatement();
			} catch (SQLException e) {
				out.println("createStatement " + e.getMessage());
				try {
					conn.close();
				} catch (SQLException e2) {
				}
				;
				return;
			}

			ResultSet rset = null;

			out.println("<html>");
			out.println("  <head><title>Operation Menu");
			out.println("  </title><base target=\"view_window\"><link type=\"text/css\" rel=\"stylesheet\" href=\"main.css\"/></head>");
			out.println("  <body>");
			out.println(" <h2>Operation Menu</h2>");

			// Generate table selections
			String query = "select table_name from user_tables order by table_name";
			try {
				rset = stmt.executeQuery(query);
			} catch (SQLException e) {
				out.println("executeQuery " + e.getMessage());
				return;
			}

			out
					.println("<form action=\"/DatabaseBrowser/ProcessTables\" method=\"post\">");
			out.println("<strong>Table List:</strong><select name=\"tName\">");
			try {
				while (rset.next()) {
					out.println("<option>" + rset.getString("table_name")
							+ "</option>");
				}
			} catch (SQLException e) {
				out.println("executeQuery " + e.getMessage());
				return;
			}
			out.println("</select>");
			out.println("<input type=\"hidden\" name=\"uid\" value=\"" + uid[0] + "\">");
			out.println("<input type=\"hidden\" name=\"uAccess\" value=\"" + uacc[0] + "\">");
			out.println("<input type=\"submit\" value=\"Go\">");
			out.println("</form>");

			// Generate procedure selections
			query = "select object_name from user_objects where object_type='PROCEDURE' order by object_name";
			try {
				rset = stmt.executeQuery(query);
			} catch (SQLException e) {
				out.println("executeQuery " + e.getMessage());
				return;
			}

			out
					.println("<form action=\"/DatabaseBrowser/ProcessObjects\" method=\"post\">");
			out
					.println("<strong>Procedures:</strong><select name=\"objName\">");
			try {
				while (rset.next()) {
					out.println("<option>" + rset.getString("object_name")
							+ "</option>");
				}
			} catch (SQLException e) {
				out.println("executeQuery " + e.getMessage());
				return;
			}
			out.println("</select>");
			out.println("<input type=\"hidden\" name=\"uid\" value=\"" + uid[0]
					+ "\">");
			out.println("<input type=\"hidden\" name=\"uAccess\" value=\""
					+ uacc[0] + "\">");
			out.println("<input type=\"submit\" value=\"Go\">");
			out.println("</form>");

			// Generate function selections
			query = "select object_name from user_objects where object_type='FUNCTION' order by object_name";
			try {
				rset = stmt.executeQuery(query);
			} catch (SQLException e) {
				out.println("executeQuery " + e.getMessage());
				return;
			}

			out.println("<form action=\"/DatabaseBrowser/ProcessObjects\" method=\"post\">");
			out.println("<strong>Functions:</strong><select name=\"objName\">");
			try {
				while (rset.next()) {
					out.println("<option>" + rset.getString("object_name")
							+ "</option>");
				}
			} catch (SQLException e) {
				out.println("executeQuery " + e.getMessage());
				return;
			}
			out.println("</select>");
			out.println("<input type=\"hidden\" name=\"uid\" value=\"" + uid[0]
					+ "\">");
			out.println("<input type=\"hidden\" name=\"uAccess\" value=\""
					+ uacc[0] + "\">");
			out.println("<input type=\"submit\" value=\"Go\">");
			out.println("</form>");

			// Generate package selections
			query = "select object_name from user_objects where object_type='PACKAGE' order by object_name";
			try {
				rset = stmt.executeQuery(query);
			} catch (SQLException e) {
				out.println("executeQuery " + e.getMessage());
				return;
			}

			out
					.println("<form action=\"/DatabaseBrowser/ProcessObjects\" method=\"post\">");
			out.println("<strong>Packages:</strong><select name=\"objName\">");
			try {
				while (rset.next()) {
					out.println("<option>" + rset.getString("object_name")
							+ "</option>");
				}
			} catch (SQLException e) {
				out.println("executeQuery " + e.getMessage());
				return;
			}
			out.println("</select>");
			out.println("<input type=\"hidden\" name=\"uid\" value=\"" + uid[0]
					+ "\">");
			out.println("<input type=\"hidden\" name=\"uAccess\" value=\""
					+ uacc[0] + "\">");
			out.println("<input type=\"submit\" value=\"Go\">");
			out.println("</form>");

			// Generate view selections
			query = "select view_name from user_views order by view_name";
			try {
				rset = stmt.executeQuery(query);
			} catch (SQLException e) {
				out.println("executeQuery " + e.getMessage());
				return;
			}

			out.println("<form action=\"/DatabaseBrowser/ProcessViews\" method=\"post\">");
			out.println("<strong>Views:</strong><select name=\"vName\">");
			try {
				while (rset.next()) {
					out.println("<option>" + rset.getString("view_name")
							+ "</option>");
				}
			} catch (SQLException e) {
				out.println("executeQuery " + e.getMessage());
				return;
			}
			out.println("</select>");
			out.println("<input type=\"hidden\" name=\"uid\" value=\"" + uid[0]+ "\">");
			out.println("<input type=\"hidden\" name=\"uAccess\" value=\"" + uacc[0] + "\">");
			out.println("<input type=\"submit\" value=\"Go\">");
			out.println("</form>");

			out.println("<BR><BR><BR><BR>");
			out.println("  </body>");
			out.println("</html>");

			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				out.println("executeQuery " + e.getMessage());
				return;
			}
			out.close();
		}

		public String getServletInfo() {
			return "This Servlet generates Member Menu";
		}

	}
